<?php

namespace app\admin\controller;

use app\admin\model\Admin;
use app\admin\model\Employ;
use app\common\controller\Api;
use app\common\controller\Backend;
use app\common\library\Auth;
use app\common\model\Department;
use app\common\model\Equipment;
use app\common\model\Fault;
use app\common\model\Hospital;
use app\common\model\News;
use think\Db;
use think\Exception;

/**
 * 二期接口
 */
class Phase extends Backend
{
    protected $noNeedLogin = ['*'];
    protected $noNeedRight = ['*'];
    protected $hospital = [];
    protected $department = [];
    protected $Ids = null;
    protected $user = null;

    public function _initialize()
    {
        parent::_initialize();
        $this->Ids=$this->auth->getGroupIds();
        $this->user=$this->auth->getUserInfo();
        if($this->Ids[0]==2) {
            $admin = Admin::get($this->user["id"]);
            if ($admin) {
            $hospital = Hospital::field("id")->where(["status" => 1])->where('FIND_IN_SET("' .$this->user["id"] . '", admin_ids)')->find();
                if($hospital){
                    $this->_hospital=$hospital;
                    if($hospital){
                        $department=Department::field("GROUP_CONCAT(id) ids")->where(["hospital_id"=>$hospital["id"]])->find();
                        if($department){
                            $this->department=$department;
                        }
                    }
                }
            }
        }
    }
    /**
     *大屏数据
     * */
    public function getRecord(){
        $sql_str=" and 1=1 ";
        $where_in="1=1";
        $where_in2="1=1";
        if($this->Ids[0]==2) {
            if(!empty($this->department)) {
                $where_in = " equipment.department_id in (" . $this->department["ids"] . ")";
                $where_in = " department_id in (" . $this->department["ids"] . ")";
                $sql_str = " and department_id in (" .  $this->department["ids"]. ")";
            }
            $list["ok_num"]=Fault::with("equipment")->where($where_in)->where(["fault.status"=>'1'])->count();//已完成的故障数
            $list["all_num"]=Fault::with("equipment")->where($where_in)->count();//总的故障数
        }else{
            $list["ok_num"]=Fault::where(["status"=>'1'])->count();//已完成的故障数
            $list["all_num"]=Fault::count();//总的故障数
        }
        $sql="SELECT COUNT(id) num FROM yl_equipment WHERE id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time < DATE_FORMAT( NOW(),'%Y-%m-%d'))
".$sql_str;
        $equipment_ok_num=Db::query($sql);//已保养设备数
        $sql="SELECT COUNT(id) num FROM yl_equipment WHERE id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d'))
".$sql_str;
        $maintained_num=Db::query($sql);//待保养设备数
        $list["maintained_num"]=$maintained_num?$maintained_num[0]["num"]:0;
        $list["equipment_ok_num"]=$equipment_ok_num?$equipment_ok_num[0]["num"]:0;//已保养设备数
        $list["equipment_num"]=Equipment::where($where_in)->count();//总的设备数
        return json(["code"=>1,"data"=>$list,"msg"=> "成功"]);
        $this->success('成功','',$list);
    }
    /**
     *故障率
     * */
    public function failure(){
        $sql_str=" and 1=1 ";
        $where_in="1=1";
        $where_in2="1=1";
        $failure=0;
        $month=0;
        $week=0;
        if($this->Ids[0]==2) {
            if(!empty($this->department)) {
                $where_in=" equipment.department_id in (".$this->department["ids"].")";
                $where_in=" department_id in (".$this->department["ids"].")";
                $sql_str=" and department_id in (".$this->department["ids"].")";
            }
            $failure=Fault::with("equipment")->where($where_in)->count();//故障数
            $month=Fault::with("equipment")->where($where_in)->whereTime('equipment.createtime','month')->count();//查询本月故障数;
            $week=Fault::with("equipment")->where($where_in)->whereTime('equipment.createtime','week')->count();//查询本周故障数;
        }else{
            $failure=Fault::count();//故障数
            $month=Fault::whereTime('createtime','month')->count();//查询本月故障数;
            $week=Fault::whereTime('createtime','week')->count();//查询本周故障数;
        }
        $list["equipment_num"]=Equipment::where($where_in)->count();//总的设备数
        $list["equipment_ure_num"]=$failure>0?sprintf("%.2f", ($failure/$list["equipment_num"]*100)):0;//设备故障率
        $list["month"]=$month>0?sprintf("%.2f", ($month/$list["equipment_num"]*100)):0;//设备本月故障率
        $list["week"]=$week>0?sprintf("%.2f", ($week/$list["equipment_num"]*100)):0;//设备本周故障率
        return json(["code"=>1,"data"=>$list,"msg"=> "成功"]);
        $this->success('成功',$list);
    }
    /**
     * 设备故障情况
     * */
    public function faultLog(){
        $sql_str=" and 1=1 ";
        $where_in=" where 1=1";
        if($this->Ids[0]==2) {
            if(!empty($this->department)) {
                $equipment=Equipment::field("GROUP_CONCAT(id) ids")->whereIn("department_id",$this->department["ids"])->find();
                if(!empty($equipment)){
                    $where_in="where id in (".$equipment["ids"].")";
                    $sql_str=" and id in (".$equipment["ids"].")";
                }
            }
        }
        $type=!$this->request->post("type")?0:$this->request->post("type");
        //重点是FROM_UNIXTIME的用法（%u是以周一为一周的开始，%U是以周日为一周的开始）

        if($type==1){
            //按周
            $sql="SELECT d.date, IFNULL(T.num,0) num FROM 
(
    SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) AS DATE
)  d
LEFT JOIN(
    SELECT FROM_UNIXTIME(createtime,'%Y-%m-%d') createtime, COUNT(id) AS num
    FROM yl_fault
    ".$where_in."
    GROUP BY FROM_UNIXTIME(createtime,'%Y-%m-%d')
) T ON T.createtime = d.date
GROUP BY d.date";
        }else{
            $sql = "SELECT COUNT(id)total,'第一周' AS data_date FROM yl_fault h
WHERE  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1) AND h.createtime >= DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY)
".$sql_str."
UNION
SELECT COUNT(id)total,'第二周' AS data_date FROM yl_fault h
WHERE  WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+1 
".$sql_str."
UNION
SELECT COUNT(id)total,'第三周' AS data_date FROM yl_fault h
WHERE    WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+2 
".$sql_str."
UNION
SELECT COUNT(id)total,'第四周' AS data_date FROM yl_fault h
WHERE   WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+3 
".$sql_str."
UNION
SELECT COUNT(id)total,'第五周' AS data_date FROM yl_fault h
WHERE WEEK(FROM_UNIXTIME(h.createtime, '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+4 AND h.createtime >= LAST_DAY(CURDATE())
".$sql_str;
        }
        $row = Db::query($sql);
        return json(["code"=>1,"data"=>$row,"msg"=> "成功"]);
        $this->success('成功',$row);
    }
    /**
     * 待保养设备 待修改
     * */
    public function maintained(){
        $sql_str=" and 1=1 ";
        if($this->Ids[0]==2) {
            if(!empty($this->department)) {
                $sql_str=" and department_id in (".$this->department["ids"].")";
            }
        }
        $type=!$this->request->post("type")?0:$this->request->post("type");
        if($type==1){
            $sql="SELECT d.date, IFNULL(T.num,0) num FROM 
(
    SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) + 0 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 1 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 2 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 3 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 4 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 5 DAY) AS DATE
UNION ALL
SELECT DATE_SUB(CURDATE(),INTERVAL WEEKDAY(CURDATE()) - 6 DAY) AS DATE
)  d
LEFT JOIN(
    SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id,a.department_id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d') ".$sql_str.") a
 GROUP BY a.createtime
) T ON T.createtime = d.date
ORDER BY d.date";
        }else{
            $sql = "SELECT COUNT(1)total,'第一周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id,a.department_id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')   ".$sql_str.") a
 GROUP BY a.createtime) h
WHERE    WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1) 
AND h.createtime >= DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY)
UNION
SELECT COUNT(1)total,'第二周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id,a.department_id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')   ".$sql_str.") a
 GROUP BY a.createtime) h
WHERE   WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+1 
UNION
SELECT COUNT(1)total,'第三周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id,a.department_id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')   ".$sql_str.") a
 GROUP BY a.createtime) h
WHERE  WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+2 
UNION
SELECT COUNT(1)total,'第四周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id,a.department_id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')   ".$sql_str.") a
 GROUP BY a.createtime) h
WHERE   WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+3 
UNION
SELECT COUNT(1)total,'第五周' AS data_date FROM (SELECT COUNT(1) num,createtime FROM ( SELECT id,TIME createtime FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) 
 AS TIME,a.id,a.department_id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c 
 WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d')   ".$sql_str.") a
 GROUP BY a.createtime) h
WHERE  WEEK(FROM_UNIXTIME(UNIX_TIMESTAMP(h.createtime), '%Y-%m-%d %H:%i:%S'), 1) = WEEK(DATE_ADD(CURDATE(), INTERVAL - DAY(CURDATE()) + 1 DAY),1)+4 AND h.createtime >= LAST_DAY(CURDATE())
";
        }
        $row["data"] = Db::query($sql);
        $sql="SELECT a.id,a.name,marking,department_id,q.name department_name  FROM yl_equipment a
JOIN yl_department q ON q.id=a.department_id
 WHERE a.id IN(
SELECT id FROM (SELECT 
 SUBSTR(SUBSTRING_INDEX(SUBSTRING_INDEX(SUBSTR(JSON_EXTRACT(a.next_json,'$[*].time'),2,LENGTH(JSON_EXTRACT(a.next_json,'$[*].time'))-2), ', ', b.help_topic_id + 1) ,', ', -1),2,10) AS TIME,a.id
FROM
 yl_equipment a JOIN mysql.help_topic b 
 ON 
 b.help_topic_id < LENGTH(JSON_EXTRACT(a.next_json,'$[*].time')) - LENGTH(REPLACE(JSON_EXTRACT(a.next_json,'$[*].time'),',','')) + 1) c WHERE c.time > DATE_FORMAT( NOW(),'%Y-%m-%d'))
".$sql_str;
        $row["all"] = Db::query($sql);//待保养设备
        return json(["code"=>1,"data"=>$row,"msg"=> "成功"]);
        $this->success('成功',$row);
    }
    /**
     *故障设备
     * */
    public function faultList(){
        $where_in="1=1 ";
        if($this->Ids[0]==2 && !empty($this->department)) {
            $where_in=" equipment.department_id in (".$this->department["ids"].")";
        }
        $list=Fault::with(["equipment"=>function($query){
            $query->withField('id,name,marking,department_id');
        }])->field("a.id,a.name")
        ->join("department a","a.id=equipment.department_id")
        ->where($where_in)
        ->select();
        return json(["code"=>1,"data"=>$list,"msg"=> "成功"]);
        $this->success('成功',$list);
    }
    /**设备操作记录
     *
     * */
    public function operationLog(){
        $sql_str="1=1 ";
        $where_in="1=1";
        if($this->Ids[0]==2) {
            if(!empty($this->department)) {
                $where_in=" a.department_id in (".$this->department["ids"].")";
                $sql_str=" equipment.department_id in (".$this->department["ids"].")";
            }
        }
        $type=!$this->request->post("type")?0:$this->request->post("type");
        $list["data"]=Employ::field("equipment_id,b.name,count(1) num")
            ->join("equipment a","yl_employ.equipment_id=a.id")
            ->join("department b","b.id=a.department_id")
            ->where($where_in)
            ->whereTime('yl_employ.createtime','week')
            ->group("equipment_id")->order("num")->limit(5)->select();//本周
        if($type==1){
            $list["data"]=Employ::field("equipment_id,b.name,count(1) num")
                ->join("equipment a","yl_employ.equipment_id=a.id")
                ->join("department b","b.id=a.department_id")
                ->where($where_in)
                ->whereTime('yl_employ.createtime','month')
                ->group("equipment_id")->order("num")->limit(5)->select();//本月
        }
        $list["all"]=Employ::field("*,FROM_UNIXTIME(employ.createtime, '%Y-%m-%d %H:%i:%S') createtime,b.name department_name")
        ->with(["equipment"=>function($query){
            $query->withField('id,name,marking,department_id');
        }])
            ->where($sql_str)
            ->join("department b","b.id=equipment.department_id")->select();
        return json(["code"=>1,"data"=>$list,"msg"=> "成功"]);
        $this->success('成功',$list);
    }

}
